using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;
namespace LdExpress
{
    public class AdminUser
    {
        public long ID = 0;
        public string UserName = "";
        public string Password = "";
        public string Email = "";
        public long IdProvince = 0;
        public DateTime DateCreate = DateTime.Now;
        public DateTime DateLogin = DateTime.Now;
        public string Location = "";
        public string Department = "";
        public int HeadOfDepartment = 0;
        public string VungIDs = "";
        public AdminUser()
        {
        }

        public AdminUser(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_adminusers";
            if (isUserExisting(this.ID, this.UserName, cn, trans) > 0)
            {
                GV.ErrorMessage = "This user '" + this.UserName + "' has been used!";
                return;
            }
            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "INSERT INTO tbl_adminusers(" +
                "UserName," +
                "PASSWORD," +
                "Email," +
                "DateCreate," +
                "DateLogin," +
                "IDProvince," +
                "Department," +
                "HeadOfDepartment," +
                "Location" +
                ",VungIDs" +
                ")" +
                "VALUES(" +
                "'" + DBModule.RefineString(UserName) + "'," +
                "'" + DBModule.RefineString(Password) + "'," +
                "'" + DBModule.RefineString(Email) + "'," +
                DBModule.RefineDatetime(DateCreate) + "," +
                DBModule.RefineDatetime(DateLogin) + "," +
                IdProvince.ToString() + "," +
                "'" + DBModule.RefineString(Department) + "'" + "," +
                HeadOfDepartment + "," +
                "'" + DBModule.RefineString(Location) + "'" +
                ",'" + DBModule.RefineString(VungIDs) + "'" +
                ");";

                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                //throw new Exception(strSQL);
                //GV.Response.Write(strSQL);
                UserObjectsLog.SaveObjectActionLog("AdminUser", "add", cn, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                             "UserName='" + DBModule.RefineString(UserName) + "'" +
                             ",Password='" + DBModule.RefineString(Password) + "'" +
                             ",Email='" + DBModule.RefineString(Email) + "'" +
                             ",DateCreate=" + DBModule.RefineDatetime(DateCreate) + "" +
                             ",DateLogin=" + DBModule.RefineDatetime(DateLogin) + "" +
                             ",Department='" + DBModule.RefineString(Department) + "'" +
                             ",Location='" + DBModule.RefineString(Location) + "'" +
                              ",HeadOfDepartment=" + HeadOfDepartment.ToString() +
                             ",IDProvince=" + IdProvince.ToString() +
                             ",VungIDs='" + DBModule.RefineString(VungIDs) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("AdminUser", "edit", cn, trans);
                //GV.Response.Write(strSQL);
            }
            //throw new Exception(strSQL);
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {

            string strSQL = "DELETE FROM tbl_AdminUsers WHERE ID = " + this.ID.ToString();

            //GV.Response.Write("sqldelete=" + strSQL);
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("AdminUser", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";


            strSQL = "SELECT * FROM tbl_AdminUsers WHERE ID = " + ID.ToString() + " limit 0,1";


            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("UserName"))
                    UserName = dr["UserName"].ToString();
                if (!dr.IsNull("Password"))
                    Password = dr["Password"].ToString();
                if (!dr.IsNull("Email"))
                    Email = dr["Email"].ToString();
                if (!dr.IsNull("DateCreate") && dr["DateCreate"].ToString().Substring(0, 8) != "0/0/0000")
                    DateCreate = DateTime.Parse(dr["DateCreate"].ToString());
                else
                    DateCreate = DateTime.Now;
                if (!dr.IsNull("DateLogin") && dr["DateCreate"].ToString().Substring(0, 8) != "0/0/0000")
                    DateLogin = DateTime.Parse(dr["DateLogin"].ToString());
                else
                    DateLogin = DateTime.Now;
                if (!dr.IsNull("IdProvince"))
                    IdProvince = long.Parse(dr["IdProvince"].ToString());
                if (!dr.IsNull("Department"))
                    Department = dr["Department"].ToString();
                if (!dr.IsNull("Location"))
                    Location = dr["Location"].ToString();
                if (!dr.IsNull("HeadOfDepartment"))
                    HeadOfDepartment = int.Parse(dr["HeadOfDepartment"].ToString());
                if (!dr.IsNull("VungIDs"))
                    VungIDs = dr["VungIDs"].ToString();

            }
        }
        public int Login(string UserNames, string Passwords, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            int retID = 0;
            try
            {
                strSQL = " Select ID from tbl_adminUsers where Username=N'" + DBModule.RefineString(UserNames) + "' and Password=N'" + Passwords + "'";// +"' and Status=1";
                DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        retID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    }
                }
                return retID;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {

            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_AdminUsers  where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += " AND `ID` in (" + strIDs + ")";
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //GV.Response.Write(strSQL);
        }

        public void DoSearch(string Condition, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_AdminUsers where 1=1 ";
            if (Condition.Length > 0)
                strSQL += Condition;
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static string GetUserNameByID(string userid, MySqlConnection cn, MySqlTransaction trans)
        {
            string retVal = "";
            string strSQL = "";
            // build SQL statement
            strSQL = "Select `UserName` from tbl_AdminUsers where ID= " + userid;
            // run SQL statement
            retVal = DBModule.ExecuteQueryForOneResult(strSQL, cn, trans);
            return retVal;
        }

        private long isUserExisting(long userid, string userName, MySqlConnection cn, MySqlTransaction trans)
        {
            long retVal = 0;
            string uID = DBModule.ExecuteQueryForOneResult("Select ID from tbl_adminUsers where ID<>" + userid +
                " AND Username='" + userName + "' limit 0,1", cn, trans);
            if (uID != null)
                retVal = long.Parse(uID);
            return retVal;
        }
    }
}

